﻿namespace Hidistro.SqlDal.Commodities
{
    using Hidistro.Core;
    using Hidistro.Entities;
    using Hidistro.Entities.Commodities;
    using Microsoft.Practices.EnterpriseLibrary.Data;
    using System;
    using System.Data;
    using System.Data.Common;

    public class CategoryDao
    {
        private Database database = DatabaseFactory.CreateDatabase();

        public int CreateCategory(CategoryInfo category)
        {
            DbCommand storedProcCommand = this.database.GetStoredProcCommand("cp_Category_Create");
            this.database.AddOutParameter(storedProcCommand, "CategoryId", DbType.Int32, 4);
            this.database.AddInParameter(storedProcCommand, "Name", DbType.String, category.Name);
            this.database.AddInParameter(storedProcCommand, "SKUPrefix", DbType.String, category.SKUPrefix);
            this.database.AddInParameter(storedProcCommand, "DisplaySequence", DbType.Int32, category.DisplaySequence);
            if (!string.IsNullOrEmpty(category.IconUrl))
            {
                this.database.AddInParameter(storedProcCommand, "Icon", DbType.String, category.Icon);
            }
            if (!string.IsNullOrEmpty(category.MetaTitle))
            {
                this.database.AddInParameter(storedProcCommand, "Meta_Title", DbType.String, category.MetaTitle);
            }
            if (!string.IsNullOrEmpty(category.MetaDescription))
            {
                this.database.AddInParameter(storedProcCommand, "Meta_Description", DbType.String, category.MetaDescription);
            }
            if (!string.IsNullOrEmpty(category.MetaKeywords))
            {
                this.database.AddInParameter(storedProcCommand, "Meta_Keywords", DbType.String, category.MetaKeywords);
            }
            if (!string.IsNullOrEmpty(category.Notes1))
            {
                this.database.AddInParameter(storedProcCommand, "Notes1", DbType.String, category.Notes1);
            }
            if (!string.IsNullOrEmpty(category.Notes2))
            {
                this.database.AddInParameter(storedProcCommand, "Notes2", DbType.String, category.Notes2);
            }
            if (!string.IsNullOrEmpty(category.Notes3))
            {
                this.database.AddInParameter(storedProcCommand, "Notes3", DbType.String, category.Notes3);
            }
            if (!string.IsNullOrEmpty(category.Notes4))
            {
                this.database.AddInParameter(storedProcCommand, "Notes4", DbType.String, category.Notes4);
            }
            if (!string.IsNullOrEmpty(category.Notes5))
            {
                this.database.AddInParameter(storedProcCommand, "Notes5", DbType.String, category.Notes5);
            }
            if (category.ParentCategoryId.HasValue)
            {
                this.database.AddInParameter(storedProcCommand, "ParentCategoryId", DbType.Int32, category.ParentCategoryId.Value);
            }
            else
            {
                this.database.AddInParameter(storedProcCommand, "ParentCategoryId", DbType.Int32, 0);
            }
            if (category.AssociatedProductType.HasValue)
            {
                this.database.AddInParameter(storedProcCommand, "AssociatedProductType", DbType.Int32, category.AssociatedProductType.Value);
            }
            if (!string.IsNullOrEmpty(category.RewriteName))
            {
                this.database.AddInParameter(storedProcCommand, "RewriteName", DbType.String, category.RewriteName);
            }
            this.database.ExecuteNonQuery(storedProcCommand);
            return (int)this.database.GetParameterValue(storedProcCommand, "CategoryId");
        }

        public bool DeleteCategory(int categoryId)
        {
            DbCommand storedProcCommand = this.database.GetStoredProcCommand("cp_Category_Delete");
            this.database.AddInParameter(storedProcCommand, "CategoryId", DbType.Int32, categoryId);
            return (this.database.ExecuteNonQuery(storedProcCommand) > 0);
        }

        public int DisplaceCategory(int oldCategoryId, int newCategory)
        {
            DbCommand sqlStringCommand = this.database.GetSqlStringCommand("UPDATE Hishop_Products SET CategoryId=@newCategory, MainCategoryPath=(SELECT Path FROM Hishop_Categories WHERE CategoryId=@newCategory)+'|' WHERE CategoryId=@oldCategoryId");
            this.database.AddInParameter(sqlStringCommand, "oldCategoryId", DbType.Int32, oldCategoryId);
            this.database.AddInParameter(sqlStringCommand, "newCategory", DbType.Int32, newCategory);
            return this.database.ExecuteNonQuery(sqlStringCommand);
        }
        public DataTable GetCategories()
        {
            DbCommand sqlStringCommand = this.database.GetSqlStringCommand("select CategoryId,Name,DisplaySequence,Meta_Title,Meta_Description,Meta_Keywords,ParentCategoryId,Depth,Path,RewriteName,(select RewriteName from Hishop_Categories WHERE CategoryId = t1.ParentCategoryId) as ParentRewriteName,SKUPrefix,AssociatedProductType,Notes1,Notes2,Notes3,Notes4,Notes5,Theme,HasChildren,Icon from Hishop_Categories t1 ORDER BY DisplaySequence");
            using (IDataReader reader = this.database.ExecuteReader(sqlStringCommand))
            {
                return DataHelper.ConverDataReaderToDataTable(reader);
            }
        }

        public CategoryInfo GetCategory(int categoryId)
        {
            string sql = "select CategoryId,Name,DisplaySequence,Meta_Title,Meta_Description,Meta_Keywords,ParentCategoryId,Depth,Path,RewriteName,(select RewriteName from Hishop_Categories WHERE CategoryId = t1.ParentCategoryId) as ParentRewriteName,SKUPrefix,AssociatedProductType,Notes1,Notes2,Notes3,Notes4,Notes5,Theme,HasChildren,Icon from Hishop_Categories t1 WHERE CategoryId =@CategoryId";
            DbCommand sqlStringCommand = this.database.GetSqlStringCommand(sql);

            this.database.AddInParameter(sqlStringCommand, "CategoryId", DbType.Int32, categoryId);
            using (IDataReader reader = this.database.ExecuteReader(sqlStringCommand))
            {
                return ReaderConvert.ReaderToModel<CategoryInfo>(reader);
            }
        }

        public CategoryInfo GetCategoryRewrite(int categoryId)
        {
            string sql = "select t1. CategoryId,Name,DisplaySequence,Meta_Title,Meta_Description,Meta_Keywords,ParentCategoryId,Depth,Path,RewriteName,(select RewriteName from Hishop_Categories WHERE CategoryId = t1.ParentCategoryId) as ParentRewriteName,SKUPrefix,AssociatedProductType,Notes1,Notes2,Notes3,Notes4,Notes5,Theme,HasChildren,Icon ,t2.* from Hishop_Categories t1 left join JS_CategoriesReview as t2 on t1.CategoryId=t2.Categoryid WHERE t1.CategoryId =@CategoryId";
            DbCommand sqlStringCommand = this.database.GetSqlStringCommand(sql);

            this.database.AddInParameter(sqlStringCommand, "CategoryId", DbType.Int32, categoryId);
            using (IDataReader reader = this.database.ExecuteReader(sqlStringCommand))
            {
                return ReaderConvert.ReaderToModel<CategoryInfo>(reader);
            }
        }
        public DataTable GetCategoryes(string categroynaem)
        {
            DbCommand sqlStringCommand = this.database.GetSqlStringCommand("SELECT CategoryId,DePth FROM Hishop_Categories WHERE [Name]=@Name");
            this.database.AddInParameter(sqlStringCommand, "Name", DbType.String, DataHelper.CleanSearchString(categroynaem));
            using (IDataReader reader = this.database.ExecuteReader(sqlStringCommand))
            {
                return DataHelper.ConverDataReaderToDataTable(reader);
            }
        }

        public DataSet GetThreeLayerCategories()
        {
            DbCommand sqlStringCommand = this.database.GetSqlStringCommand("SELECT Name,CategoryId,Icon,RewriteName FROM Hishop_Categories WHERE ParentCategoryId=0 AND Depth = 1 ORDER BY DisplaySequence; SELECT ParentCategoryId,Name,Icon,CategoryId,RewriteName FROM Hishop_Categories WHERE Depth = 2 ORDER BY DisplaySequence; SELECT ParentCategoryId,Name,Icon,CategoryId,RewriteName FROM Hishop_Categories WHERE Depth = 3 ORDER BY DisplaySequence;");
            DataSet set = this.database.ExecuteDataSet(sqlStringCommand);
            set.Relations.Add("One", set.Tables[0].Columns["CategoryId"], set.Tables[1].Columns["ParentCategoryId"], false);
            set.Relations.Add("Two", set.Tables[1].Columns["CategoryId"], set.Tables[2].Columns["ParentCategoryId"], false);
            return set;
        }

        public bool SetCategoryThemes(int categoryId, string themeName)
        {
            DbCommand sqlStringCommand = this.database.GetSqlStringCommand("UPDATE Hishop_Categories SET Theme = @Theme WHERE CategoryId = @CategoryId");
            this.database.AddInParameter(sqlStringCommand, "CategoryId", DbType.Int32, categoryId);
            this.database.AddInParameter(sqlStringCommand, "Theme", DbType.String, themeName);
            return (this.database.ExecuteNonQuery(sqlStringCommand) == 1);
        }

        public bool SetProductExtendCategory(int productId, string extendCategoryPath)
        {
            DbCommand sqlStringCommand = this.database.GetSqlStringCommand("UPDATE Hishop_Products SET ExtendCategoryPath = @ExtendCategoryPath WHERE ProductId = @ProductId");
            this.database.AddInParameter(sqlStringCommand, "ProductId", DbType.Int32, productId);
            this.database.AddInParameter(sqlStringCommand, "ExtendCategoryPath", DbType.String, extendCategoryPath);
            return (this.database.ExecuteNonQuery(sqlStringCommand) == 1);
        }

        public bool SwapCategorySequence(int categoryId, int displaysequence)
        {
            DbCommand sqlStringCommand = this.database.GetSqlStringCommand("update Hishop_Categories  set DisplaySequence=@DisplaySequence where CategoryId=@CategoryId");
            this.database.AddInParameter(sqlStringCommand, "@DisplaySequence", DbType.Int32, displaysequence);
            this.database.AddInParameter(sqlStringCommand, "@CategoryId", DbType.Int32, categoryId);
            return (this.database.ExecuteNonQuery(sqlStringCommand) > 0);
        }

        public CategoryActionStatus UpdateCategory(CategoryInfo category)
        {
            DbCommand sqlStringCommand = this.database.GetSqlStringCommand("UPDATE Hishop_Categories SET [Name] = @Name, SKUPrefix = @SKUPrefix,AssociatedProductType = @AssociatedProductType, Meta_Title=@Meta_Title,Meta_Description = @Meta_Description, Icon = @IconUrl,Meta_Keywords = @Meta_Keywords, Notes1 = @Notes1, Notes2 = @Notes2, Notes3 = @Notes3,  Notes4 = @Notes4, Notes5 = @Notes5, RewriteName = @RewriteName WHERE CategoryId = @CategoryId;UPDATE Hishop_Categories SET RewriteName = @RewriteName WHERE ParentCategoryId = @CategoryId");
            this.database.AddInParameter(sqlStringCommand, "CategoryId", DbType.Int32, category.CategoryId);
            this.database.AddInParameter(sqlStringCommand, "Name", DbType.String, category.Name);
            this.database.AddInParameter(sqlStringCommand, "SKUPrefix", DbType.String, category.SKUPrefix);
            this.database.AddInParameter(sqlStringCommand, "AssociatedProductType", DbType.Int32, category.AssociatedProductType);
            this.database.AddInParameter(sqlStringCommand, "Meta_Title", DbType.String, category.MetaTitle);
            this.database.AddInParameter(sqlStringCommand, "Meta_Description", DbType.String, category.MetaDescription);
            this.database.AddInParameter(sqlStringCommand, "IconUrl", DbType.String, category.Icon);
            this.database.AddInParameter(sqlStringCommand, "Meta_Keywords", DbType.String, category.MetaKeywords);
            this.database.AddInParameter(sqlStringCommand, "Notes1", DbType.String, category.Notes1);
            this.database.AddInParameter(sqlStringCommand, "Notes2", DbType.String, category.Notes2);
            this.database.AddInParameter(sqlStringCommand, "Notes3", DbType.String, category.Notes3);
            this.database.AddInParameter(sqlStringCommand, "Notes4", DbType.String, category.Notes4);
            this.database.AddInParameter(sqlStringCommand, "Notes5", DbType.String, category.Notes5);
            this.database.AddInParameter(sqlStringCommand, "RewriteName", DbType.String, category.RewriteName);
            return ((this.database.ExecuteNonQuery(sqlStringCommand) >= 1) ? CategoryActionStatus.Success : CategoryActionStatus.UnknowError);
        }

        public CategoryInfo GetCategoryByRewrite(string Rewrite)
        {
            DbCommand sqlStringCommand = this.database.GetSqlStringCommand("select CategoryId,Name,DisplaySequence,Meta_Title,Meta_Description,Meta_Keywords,ParentCategoryId,Depth,Path,RewriteName,(select RewriteName from Hishop_Categories WHERE CategoryId = t1.ParentCategoryId) as ParentRewriteName,SKUPrefix,AssociatedProductType,Notes1,Notes2,Notes3,Notes4,Notes5,Theme,HasChildren,Icon from Hishop_Categories t1 where RewriteName = @RewriteName");
            this.database.AddInParameter(sqlStringCommand, "RewriteName", DbType.String, Rewrite);
            using (IDataReader reader = this.database.ExecuteReader(sqlStringCommand))
            {
                return ReaderConvert.ReaderToModel<CategoryInfo>(reader);
            }
        }
    }
}

